import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

/**
 * 统计各地区点击数量Top3商品
 */
object Req02 {
  def main(args: Array[String]): Unit = {

    val sparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSQL")
    val spark = SparkSession.builder().enableHiveSupport().config(sparkConf).getOrCreate()

    spark.sql("use chenL")

    spark.sql(
      """
        |select
        |    *
        |from(
        |    select
        |        t2.*,
        |        rank() over(partition by area order by t2.clickCnt desc) rank
        |    from(
        |        select
        |            t1.area,
        |            t1.product_name,
        |            count(*) clickCnt
        |        from(
        |        select
        |            u.*,
        |            p.product_name,
        |            c.area,c.city_name
        |        from
        |            user_visit_action u
        |            join city_info c on u.city_id = c.city_id
        |            join product_info p on u.click_product_id = p.product_id
        |        where
        |            u.click_product_id > 0
        |        )t1 group by area, product_name
        |    )t2
        |)t3
        |where t3.rank < 4
        |""".stripMargin).show()

    spark.close()
  }

}
